<%@ page import="java.sql.Statement" import="java.sql.ResultSet" import="java.sql.Connection" %>
<%@page import="connection.DBConnection"%>
<%@page import="java.util.*" %>
<%@page import="java.text.SimpleDateFormat" %>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Purchase Other Costs</title>
</head>
<body>

<h2 align="center"> Purchase Other Costs</h2>
<form name="purchaeseothercostsamount" method="post">

  <%
        String exportToExcel = request.getParameter("exportToExcel");
        if (exportToExcel != null && exportToExcel.toString().equalsIgnoreCase("YES")) 
        {
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "inline; filename=" + "PurchaseOtherCosts.xls");
 
        }
    %>

<table border=1 align="center"> 
<tr>
	<th>Invoice Number</th>
	<th>Date</th>
	<th>Supplier Name</th>
	<th>Other Costs</th>
</tr>

		<%  DBConnection dbc=new DBConnection();
			Connection con=dbc.getNewConnection();
			Statement st = null;
			ResultSet rs = null;
			
			double pot=0.0,ptotal=0.0;

			try
			{
			   st=con.createStatement() ;
       		   rs=st.executeQuery("select pvinno,date,pvsname,pvot from PurchaseVoucher2"); %>
	        	
       		 <%  while(rs.next()){ %>
<tr>
					<% String v1= rs.getString(1); %>
            		<td><a href="http://localhost:8081/BOA_Web/LedgerReportServlet?vouchernumber=<%=v1%>&group=Purchase"> <%=v1 %></a> </td>
            		
            		<% String svdate= rs.getString(2); 
           	    		 SimpleDateFormat fromsql = new SimpleDateFormat("yyyy-MM-dd");
        				 SimpleDateFormat formatter = new SimpleDateFormat("dd-MM-yyyy");
        		 		 String newdate =formatter.format(fromsql.parse(svdate));
            		%>
            		<td><%=newdate %></td>
            		<td><%= rs.getString(3)%></td>
            		
            		<% pot= rs.getDouble(4); %>
            		<td> <%=pot %> </td>
            		            		
</tr>
        		<%  ptotal=ptotal+pot;
	    			} 
			 }
			catch(Exception e){} %>
			
			
		<%  			
			double dbot=0.0,dbtotal=0.0;

			try
			{
			   st=con.createStatement() ;
       		   rs=st.executeQuery("select dbinno,date,dbsname,-(dbot) from DebitNote"); %>
	        	
       		 <%  while(rs.next()){ %>
<tr>
					<% String v1= rs.getString(1); %>
            		<td><a href="http://localhost:8081/BOA_Web/LedgerReportServlet?vouchernumber=<%=v1%>&group=Debit"> <%=v1 %></a> </td>
            		
            		<% String dbdate= rs.getString(2); 
           	    		 SimpleDateFormat fromsql = new SimpleDateFormat("yyyy-MM-dd");
        				 SimpleDateFormat formatter = new SimpleDateFormat("dd-MM-yyyy");
        		 		 String newdate =formatter.format(fromsql.parse(dbdate));
            		%>
            		<td><%=newdate %></td>
            		<td><%= rs.getString(3)%></td>
            		
            		<% dbot= rs.getDouble(4); %>
            		<td> <%=dbot %> </td>
            		            		
</tr>
        		<%  dbtotal=dbtotal+dbot;
	    			} 
  			 }
			catch(Exception e){} %>
		
<tr>
	<td> <b> Total </b></td>
	<td> </td>
	<td> </td>
	<td> <%=ptotal+dbtotal %></td>
</tr>	
			
</table>
<br><br> 
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

    <%
        if (exportToExcel == null) {
    %>
    <a href="PurchaseOtherCosts.jsp?exportToExcel=YES">Export to Excel</a>
    <%
        }
    %>
</form>

</body>
</html>